GO
/****** Object:  Table [dbo].[ActivityLog]    Script Date: 4/10/2015 11:50:35 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[ActivityLog](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[RecordID] [int] NOT NULL,
	[RecordName] [varchar](50) NOT NULL,
	[ChangeType] [varchar](6) NOT NULL,
	[ChangeDate] [datetime] NOT NULL,
	[UserName] [varchar](100) NOT NULL,
	[RecordType] [varchar](100) NOT NULL,
 CONSTRAINT [PK_ActivityLog] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[ActivityLogDetail]    Script Date: 4/10/2015 11:50:35 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[ActivityLogDetail](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[ActivityLogID] [int] NOT NULL,
	[OldValue] [nvarchar](max) NULL,
	[NewValue] [nvarchar](max) NULL,
	[FieldName] [varchar](50) NOT NULL,
 CONSTRAINT [PK_ActivityLogDetail] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[Alarm]    Script Date: 4/10/2015 11:50:35 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Alarm](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[EscalationProfileID] [int] NOT NULL,
	[AcknowledgedBy] [varchar](50) NULL,
	[DateAdded] [datetime] NOT NULL,
	[DateAcknowledged] [datetime] NULL,
	[Acknowledged] [bit] NOT NULL,
	[TrapMonitorID] [int] NULL,
	[Message] [varchar](500) NOT NULL,
	[TrapID] [int] NULL,
	[ElementID] [int] NOT NULL,
	[Count] [int] NOT NULL,
	[DateUpdated] [datetime] NOT NULL,
	[ElementMonitorID] [int] NULL,
 CONSTRAINT [PK_ActiveAlarms] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[AlarmLog]    Script Date: 4/10/2015 11:50:35 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[AlarmLog](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[AlarmID] [int] NOT NULL,
	[DateSent] [datetime] NOT NULL,
	[Details] [varchar](max) NOT NULL,
	[SentTo] [varchar](50) NULL,
	[Type] [varchar](10) NULL,
	[EscalationActionID] [int] NOT NULL,
	[SendSuccess] [bit] NULL,
	[SendMessage] [varchar](100) NULL,
 CONSTRAINT [PK_AlarmLog] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[Avaya_CallSummary]    Script Date: 4/10/2015 11:50:35 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Avaya_CallSummary](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[VoiceSystemID] [int] NOT NULL,
	[DateRun] [datetime] NOT NULL,
	[Hour] [int] NULL,
	[Voice] [int] NULL,
	[Data] [int] NULL,
	[Media] [int] NULL,
	[Link] [int] NULL,
	[Total] [int] NULL,
 CONSTRAINT [PK_Avaya_CallRateMeasurement] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[Avaya_TrunkGroupSummary]    Script Date: 4/10/2015 11:50:35 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Avaya_TrunkGroupSummary](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[VoiceSystemID] [int] NOT NULL,
	[DateRun] [datetime] NOT NULL,
	[GrpNum] [int] NULL,
	[GrpSiz] [int] NULL,
	[GrpType] [varchar](20) NULL,
	[GrpDir] [varchar](20) NULL,
	[MeasHour] [int] NULL,
	[TotalUsage] [int] NULL,
	[TotalSeize] [int] NULL,
	[IncSeize] [int] NULL,
	[GrpOvfl] [int] NULL,
	[QueSiz] [int] NULL,
	[CallQued] [int] NULL,
	[QueOvf] [int] NULL,
	[QueAbd] [int] NULL,
	[OutSrv] [int] NULL,
	[PercentATB] [int] NULL,
	[PercentOutBlk] [int] NULL,
 CONSTRAINT [PK_Avaya_TrunkGroupSummary] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[CairsCallRecords]    Script Date: 4/10/2015 11:50:35 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[CairsCallRecords](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[SwitchID] [uniqueidentifier] NULL,
	[Calls] [int] NOT NULL,
	[Seconds] [int] NOT NULL,
	[Trunk] [varchar](50) NULL,
	[Hour] [int] NOT NULL,
	[Month] [int] NOT NULL,
	[Day] [int] NOT NULL,
	[Year] [int] NOT NULL,
 CONSTRAINT [PK_CairsCallRecords] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[CS2100RawAlarms]    Script Date: 4/10/2015 11:50:35 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[CS2100RawAlarms](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[AlarmData] [varchar](max) NOT NULL,
	[ElementID] [int] NOT NULL,
	[DateAdded] [datetime] NOT NULL,
 CONSTRAINT [PK_CS2100RawAlarms] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[Element]    Script Date: 4/10/2015 11:50:35 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Element](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Name] [varchar](50) NOT NULL,
	[IpAddress] [varchar](15) NOT NULL,
	[Port] [int] NOT NULL,
	[CommunityName] [varchar](50) NULL,
	[UserName] [varchar](50) NULL,
	[AuthProtocol] [varchar](15) NULL,
	[AuthPassword] [varchar](50) NULL,
	[PrivacyProtocol] [varchar](15) NULL,
	[PrivacyPassword] [varchar](50) NULL,
	[Version] [varchar](5) NULL,
	[SNMPSendTimeout] [int] NULL,
	[SNMPReceiveTimeout] [int] NULL,
	[Type] [varchar](50) NOT NULL,
	[PropertyDate] [datetime] NULL,
	[Xpos] [int] NOT NULL,
	[Ypos] [int] NOT NULL,
	[CollectorIP] [varchar](25) NULL,
	[CollectorPort] [int] NOT NULL,
	[FilePath] [varchar](200) NULL,
 CONSTRAINT [PK_AlarmElement] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[ElementCollectionLog]    Script Date: 4/10/2015 11:50:35 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[ElementCollectionLog](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[ElementID] [int] NOT NULL,
	[DateAdded] [datetime] NOT NULL,
	[Data] [varchar](max) NOT NULL,
 CONSTRAINT [PK_ElementCollectionLog] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[EscalationAction]    Script Date: 4/10/2015 11:50:35 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[EscalationAction](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[EscalationProfileID] [int] NOT NULL,
	[Order] [int] NOT NULL,
	[Name] [varchar](50) NOT NULL,
	[PhoneEmail] [varchar](100) NOT NULL,
	[ResponseTime] [int] NOT NULL,
	[Recurring] [bit] NOT NULL,
	[Type] [varchar](50) NOT NULL,
 CONSTRAINT [PK_EscalationAction] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[EscalationProfile]    Script Date: 4/10/2015 11:50:35 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[EscalationProfile](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Name] [varchar](50) NOT NULL,
 CONSTRAINT [PK_EscalationProfile] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[ReportCommand]    Script Date: 4/10/2015 11:50:35 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[ReportCommand](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Command1] [varchar](50) NULL,
	[Command2] [varchar](50) NULL,
	[Command3] [varchar](50) NULL,
	[Command4] [varchar](50) NULL,
 CONSTRAINT [PK_ReportCommand] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[ReportParseLog]    Script Date: 4/10/2015 11:50:35 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[ReportParseLog](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[ReportRunLogID] [int] NOT NULL,
	[DateRun] [datetime] NOT NULL,
	[Status] [varchar](25) NOT NULL,
	[ErrorMessage] [varchar](max) NULL,
 CONSTRAINT [PK_ReportParseLog] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[ReportRunLog]    Script Date: 4/10/2015 11:50:35 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[ReportRunLog](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[VoiceSystemID] [int] NOT NULL,
	[ReportName] [varchar](50) NOT NULL,
	[DateRun] [datetime] NOT NULL,
	[Message] [varchar](500) NULL,
	[ReportRunnerID] [int] NULL,
	[Status] [varchar](25) NOT NULL,
	[SwitchData] [varchar](max) NULL,
	[RunAttempts] [int] NOT NULL,
 CONSTRAINT [PK_Run] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[ReportRunner]    Script Date: 4/10/2015 11:50:35 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[ReportRunner](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[VoiceSystemID] [int] NOT NULL,
	[ReportName] [varchar](50) NOT NULL,
	[Enabled] [bit] NOT NULL,
	[RunTime] [varchar](25) NOT NULL,
	[Running] [bit] NOT NULL,
	[ReportCommandID] [int] NULL,
	[StartText] [varchar](250) NULL,
	[EndText] [varchar](250) NULL,
 CONSTRAINT [PK_ReportRunner] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[SchemaChange]    Script Date: 4/10/2015 11:50:35 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[SchemaChange](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[DatabaseVersion] [float] NOT NULL,
	[ScriptName] [varchar](50) NOT NULL,
	[Notes] [varchar](max) NULL,
	[DateApplied] [datetime] NOT NULL,
 CONSTRAINT [PK_SchemaChange] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[SystemLog]    Script Date: 4/10/2015 11:50:35 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[SystemLog](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[DateCreated] [datetime] NOT NULL,
	[Message] [varchar](max) NOT NULL,
	[Severity] [varchar](12) NOT NULL,
 CONSTRAINT [PK_SystemErrorLog] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[SystemSetting]    Script Date: 4/10/2015 11:50:35 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[SystemSetting](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Name] [varchar](50) NOT NULL,
	[Value] [varchar](max) NOT NULL,
 CONSTRAINT [PK_SystemSettings] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[Trap]    Script Date: 4/10/2015 11:50:35 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Trap](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[ElementID] [int] NOT NULL,
	[DateReceived] [datetime] NOT NULL,
	[SNMPVersion] [varchar](50) NULL,
	[OriginIP] [varchar](50) NULL,
	[DestinationIP] [varchar](50) NULL,
	[Community] [varchar](50) NULL,
	[Message] [varchar](max) NULL,
	[Enterprise] [varchar](50) NULL,
	[User] [varchar](50) NULL,
 CONSTRAINT [PK_Alarm] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[TrapMonitor]    Script Date: 4/10/2015 11:50:35 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[TrapMonitor](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[ElementID] [int] NOT NULL,
	[Name] [varchar](50) NOT NULL,
	[Oid] [varchar](50) NOT NULL,
	[EscalationProfileID] [int] NOT NULL,
	[Message] [varchar](400) NULL,
	[Value] [varchar](50) NULL,
 CONSTRAINT [PK_AlarmConfiguration] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[User]    Script Date: 4/10/2015 11:50:35 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[User](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Login] [varchar](100) NOT NULL,
	[Email] [varchar](100) NULL,
	[Name] [varchar](100) NOT NULL,
	[PasswordHash] [varchar](50) NULL,
	[PasswordSalt] [varchar](50) NULL,
	[Profile] [varchar](12) NOT NULL,
	[SMSNumber] [varchar](20) NULL,
	[CertificateBytes] [varbinary](max) NULL,
	[Enabled] [bit] NOT NULL,
	[DateAdded] [datetime] NOT NULL,
	[LastLogin] [datetime] NULL,
	[CertificateSerialNumber] [varchar](50) NULL,
 CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[Version]    Script Date: 4/10/2015 11:50:35 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Version](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[CurrentVersion] [varchar](50) NOT NULL,
	[DefaultsLoaded] [bit] NOT NULL,
	[DateDefaultsLoaded] [datetime] NOT NULL,
 CONSTRAINT [PK_Version] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[VoiceSystem]    Script Date: 4/10/2015 11:50:35 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[VoiceSystem](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Name] [varchar](50) NOT NULL,
	[SwitchType] [varchar](25) NOT NULL,
	[SwitchHost] [varchar](50) NULL,
	[SwitchPort] [int] NOT NULL,
	[SwitchUsername] [varchar](50) NULL,
	[SwitchPassword] [nchar](10) NULL,
	[SwitchConnectionType] [varchar](25) NULL,
	[TelebossHost] [varchar](25) NULL,
	[TelebossUsername] [varchar](50) NULL,
	[TelebossPassword] [varchar](50) NULL,
	[TelebossPort] [int] NOT NULL,
 CONSTRAINT [PK_Switch] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[VoiceSystemSession]    Script Date: 4/10/2015 11:50:35 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[VoiceSystemSession](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[VoiceSystemID] [int] NOT NULL,
	[DateRun] [datetime] NOT NULL,
	[Data] [varchar](max) NOT NULL,
 CONSTRAINT [PK_SwitchSession] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  View [dbo].[Avaya_CallSummaryView]    Script Date: 4/10/2015 11:50:35 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[Avaya_CallSummaryView]
AS
SELECT        VoiceSystemID, DateRun, Hour, SUM(Voice) AS Voice, SUM(Data) AS Data, SUM(Media) AS Media, SUM(Link) AS Link, SUM(Total) AS Total
FROM            dbo.Avaya_CallSummary
GROUP BY DateRun, Hour, VoiceSystemID

GO
/****** Object:  View [dbo].[Avaya_TrunkGroupSummaryView]    Script Date: 4/10/2015 11:50:35 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[Avaya_TrunkGroupSummaryView]
AS
SELECT        VoiceSystemID, DateRun, MeasHour, GrpNum, GrpSiz, GrpType, GrpDir, SUM(TotalUsage) AS TotalUsage, SUM(TotalSeize) AS TotalSeize, SUM(PercentATB) AS TotalATB, SUM(PercentOutBlk) 
                         AS TotalOutBlk
FROM            dbo.Avaya_TrunkGroupSummary
GROUP BY VoiceSystemID, MeasHour, DateRun, GrpNum, GrpSiz, GrpType, GrpDir

GO
ALTER TABLE [dbo].[ActivityLog] ADD  CONSTRAINT [DF_ActivityLog_RecordID]  DEFAULT ((0)) FOR [RecordID]
GO
ALTER TABLE [dbo].[ActivityLog] ADD  CONSTRAINT [DF_ActivityLog_ChangeDate]  DEFAULT (getdate()) FOR [ChangeDate]
GO
ALTER TABLE [dbo].[ActivityLog] ADD  CONSTRAINT [DF_ActivityLog_RecordType]  DEFAULT ('Unknown') FOR [RecordType]
GO
ALTER TABLE [dbo].[Alarm] ADD  CONSTRAINT [DF_ActiveAlarms_DateAdded]  DEFAULT (getdate()) FOR [DateAdded]
GO
ALTER TABLE [dbo].[Alarm] ADD  CONSTRAINT [DF_Alarm_Acknowledged]  DEFAULT ((0)) FOR [Acknowledged]
GO
ALTER TABLE [dbo].[Alarm] ADD  CONSTRAINT [DF_Alarm_Message]  DEFAULT ('Not Set') FOR [Message]
GO
ALTER TABLE [dbo].[Alarm] ADD  CONSTRAINT [DF_Alarm_Count]  DEFAULT ((1)) FOR [Count]
GO
ALTER TABLE [dbo].[Alarm] ADD  CONSTRAINT [DF_Alarm_DateUpdated]  DEFAULT (getdate()) FOR [DateUpdated]
GO
ALTER TABLE [dbo].[AlarmLog] ADD  CONSTRAINT [DF_AlarmLog_DateSent]  DEFAULT (getdate()) FOR [DateSent]
GO
ALTER TABLE [dbo].[Avaya_TrunkGroupSummary] ADD  CONSTRAINT [DF_Avaya_TrunkGroupSummary_DateRun]  DEFAULT (getdate()) FOR [DateRun]
GO
ALTER TABLE [dbo].[CS2100RawAlarms] ADD  CONSTRAINT [DF_CS2100RawAlarms_DateAdded]  DEFAULT (getdate()) FOR [DateAdded]
GO
ALTER TABLE [dbo].[Element] ADD  CONSTRAINT [DF_Element_Port]  DEFAULT ((161)) FOR [Port]
GO
ALTER TABLE [dbo].[Element] ADD  CONSTRAINT [DF_Element_SendTimeout]  DEFAULT ((200)) FOR [SNMPSendTimeout]
GO
ALTER TABLE [dbo].[Element] ADD  CONSTRAINT [DF_Element_ReceiveTimeout]  DEFAULT ((200)) FOR [SNMPReceiveTimeout]
GO
ALTER TABLE [dbo].[Element] ADD  CONSTRAINT [DF_Element_Type]  DEFAULT ('Unknown') FOR [Type]
GO
ALTER TABLE [dbo].[Element] ADD  CONSTRAINT [DF_Element_Xpos]  DEFAULT ((0)) FOR [Xpos]
GO
ALTER TABLE [dbo].[Element] ADD  CONSTRAINT [DF_Element_Ypos]  DEFAULT ((0)) FOR [Ypos]
GO
ALTER TABLE [dbo].[Element] ADD  CONSTRAINT [DF_Element_CollectorPort]  DEFAULT ((0)) FOR [CollectorPort]
GO
ALTER TABLE [dbo].[ElementCollectionLog] ADD  CONSTRAINT [DF_ElementCollectionLog_DateAdded]  DEFAULT (getdate()) FOR [DateAdded]
GO
ALTER TABLE [dbo].[EscalationAction] ADD  CONSTRAINT [DF_EscalationAction_Order]  DEFAULT ((0)) FOR [Order]
GO
ALTER TABLE [dbo].[EscalationAction] ADD  CONSTRAINT [DF_EscalationAction_Recurring]  DEFAULT ((0)) FOR [Recurring]
GO
ALTER TABLE [dbo].[ReportParseLog] ADD  CONSTRAINT [DF_ReportParseLog_DateRun]  DEFAULT (getdate()) FOR [DateRun]
GO
ALTER TABLE [dbo].[ReportRunLog] ADD  CONSTRAINT [DF_Run_DateRun]  DEFAULT (getdate()) FOR [DateRun]
GO
ALTER TABLE [dbo].[ReportRunLog] ADD  CONSTRAINT [DF_RunLog_Status]  DEFAULT ('Success') FOR [Status]
GO
ALTER TABLE [dbo].[ReportRunLog] ADD  CONSTRAINT [DF_RunLog_RunAttempts]  DEFAULT ((0)) FOR [RunAttempts]
GO
ALTER TABLE [dbo].[ReportRunner] ADD  CONSTRAINT [DF_ReportRunner_Enabled]  DEFAULT ((1)) FOR [Enabled]
GO
ALTER TABLE [dbo].[ReportRunner] ADD  CONSTRAINT [DF_ReportRunner_RunTime]  DEFAULT ('24-Hour') FOR [RunTime]
GO
ALTER TABLE [dbo].[ReportRunner] ADD  CONSTRAINT [DF_ReportRunner_Running]  DEFAULT ((0)) FOR [Running]
GO
ALTER TABLE [dbo].[SchemaChange] ADD  CONSTRAINT [DF_SchemaChange_DatabaseVersion]  DEFAULT ((0)) FOR [DatabaseVersion]
GO
ALTER TABLE [dbo].[SchemaChange] ADD  CONSTRAINT [DF_SchemaChange_DateApplied]  DEFAULT (getdate()) FOR [DateApplied]
GO
ALTER TABLE [dbo].[SystemLog] ADD  CONSTRAINT [DF_SystemErrorLog_DateCreated]  DEFAULT (getdate()) FOR [DateCreated]
GO
ALTER TABLE [dbo].[SystemLog] ADD  CONSTRAINT [DF_SystemLog_Severity]  DEFAULT ('Info') FOR [Severity]
GO
ALTER TABLE [dbo].[Trap] ADD  CONSTRAINT [DF_Alarms_DateReceived]  DEFAULT (getdate()) FOR [DateReceived]
GO
ALTER TABLE [dbo].[User] ADD  CONSTRAINT [DF_User_Profile]  DEFAULT ('User') FOR [Profile]
GO
ALTER TABLE [dbo].[User] ADD  CONSTRAINT [DF_User_Enabled_1]  DEFAULT ((0)) FOR [Enabled]
GO
ALTER TABLE [dbo].[User] ADD  CONSTRAINT [DF_User_DateAdded_1]  DEFAULT (getdate()) FOR [DateAdded]
GO
ALTER TABLE [dbo].[Version] ADD  CONSTRAINT [DF_Version_DateDefaultsLoaded]  DEFAULT (getdate()) FOR [DateDefaultsLoaded]
GO
ALTER TABLE [dbo].[VoiceSystem] ADD  CONSTRAINT [DF_Switch_SwitchPort]  DEFAULT ((0)) FOR [SwitchPort]
GO
ALTER TABLE [dbo].[VoiceSystem] ADD  CONSTRAINT [DF_Switch_TelebossPort]  DEFAULT ((0)) FOR [TelebossPort]
GO
ALTER TABLE [dbo].[VoiceSystemSession] ADD  CONSTRAINT [DF_SwitchSession_DateRun]  DEFAULT (getdate()) FOR [DateRun]
GO
ALTER TABLE [dbo].[ActivityLogDetail]  WITH CHECK ADD  CONSTRAINT [FK_ActivityLogDetail_ActivityLog] FOREIGN KEY([ActivityLogID])
REFERENCES [dbo].[ActivityLog] ([ID])
GO
ALTER TABLE [dbo].[ActivityLogDetail] CHECK CONSTRAINT [FK_ActivityLogDetail_ActivityLog]
GO
ALTER TABLE [dbo].[Alarm]  WITH CHECK ADD  CONSTRAINT [FK_ActiveAlarms_EscalationProfile] FOREIGN KEY([EscalationProfileID])
REFERENCES [dbo].[EscalationProfile] ([ID])
GO
ALTER TABLE [dbo].[Alarm] CHECK CONSTRAINT [FK_ActiveAlarms_EscalationProfile]
GO
ALTER TABLE [dbo].[Alarm]  WITH CHECK ADD  CONSTRAINT [FK_Alarm_Element] FOREIGN KEY([ElementID])
REFERENCES [dbo].[Element] ([ID])
GO
ALTER TABLE [dbo].[Alarm] CHECK CONSTRAINT [FK_Alarm_Element]
GO
ALTER TABLE [dbo].[Alarm]  WITH CHECK ADD  CONSTRAINT [FK_Alarm_Trap1] FOREIGN KEY([TrapID])
REFERENCES [dbo].[Trap] ([ID])
GO
ALTER TABLE [dbo].[Alarm] CHECK CONSTRAINT [FK_Alarm_Trap1]
GO
ALTER TABLE [dbo].[Alarm]  WITH CHECK ADD  CONSTRAINT [FK_Alarm_TrapMonitor] FOREIGN KEY([TrapMonitorID])
REFERENCES [dbo].[TrapMonitor] ([ID])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[Alarm] CHECK CONSTRAINT [FK_Alarm_TrapMonitor]
GO
ALTER TABLE [dbo].[AlarmLog]  WITH CHECK ADD  CONSTRAINT [FK_AlarmLog_ActiveAlarm] FOREIGN KEY([AlarmID])
REFERENCES [dbo].[Alarm] ([ID])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[AlarmLog] CHECK CONSTRAINT [FK_AlarmLog_ActiveAlarm]
GO
ALTER TABLE [dbo].[AlarmLog]  WITH CHECK ADD  CONSTRAINT [FK_AlarmLog_Escalation] FOREIGN KEY([EscalationActionID])
REFERENCES [dbo].[EscalationAction] ([ID])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[AlarmLog] CHECK CONSTRAINT [FK_AlarmLog_Escalation]
GO
ALTER TABLE [dbo].[Avaya_CallSummary]  WITH CHECK ADD  CONSTRAINT [FK_Avaya_CallSummary_Switch] FOREIGN KEY([VoiceSystemID])
REFERENCES [dbo].[VoiceSystem] ([ID])
GO
ALTER TABLE [dbo].[Avaya_CallSummary] CHECK CONSTRAINT [FK_Avaya_CallSummary_Switch]
GO
ALTER TABLE [dbo].[Avaya_TrunkGroupSummary]  WITH CHECK ADD  CONSTRAINT [FK_Avaya_TrunkGroupSummary_Switch] FOREIGN KEY([VoiceSystemID])
REFERENCES [dbo].[VoiceSystem] ([ID])
GO
ALTER TABLE [dbo].[Avaya_TrunkGroupSummary] CHECK CONSTRAINT [FK_Avaya_TrunkGroupSummary_Switch]
GO
ALTER TABLE [dbo].[CS2100RawAlarms]  WITH CHECK ADD  CONSTRAINT [fk_Element_CS2100RawAlarms] FOREIGN KEY([ElementID])
REFERENCES [dbo].[Element] ([ID])
GO
ALTER TABLE [dbo].[CS2100RawAlarms] CHECK CONSTRAINT [fk_Element_CS2100RawAlarms]
GO
ALTER TABLE [dbo].[ElementCollectionLog]  WITH CHECK ADD  CONSTRAINT [fk_Element_ElementCollectionLog] FOREIGN KEY([ElementID])
REFERENCES [dbo].[Element] ([ID])
GO
ALTER TABLE [dbo].[ElementCollectionLog] CHECK CONSTRAINT [fk_Element_ElementCollectionLog]
GO
ALTER TABLE [dbo].[EscalationAction]  WITH CHECK ADD  CONSTRAINT [FK_EscalationAction_EscalationProfile] FOREIGN KEY([EscalationProfileID])
REFERENCES [dbo].[EscalationProfile] ([ID])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[EscalationAction] CHECK CONSTRAINT [FK_EscalationAction_EscalationProfile]
GO
ALTER TABLE [dbo].[ReportParseLog]  WITH CHECK ADD  CONSTRAINT [FK_ReportParseLog_ReportRunLog] FOREIGN KEY([ReportRunLogID])
REFERENCES [dbo].[ReportRunLog] ([ID])
GO
ALTER TABLE [dbo].[ReportParseLog] CHECK CONSTRAINT [FK_ReportParseLog_ReportRunLog]
GO
ALTER TABLE [dbo].[ReportRunLog]  WITH CHECK ADD  CONSTRAINT [FK_Run_Switch] FOREIGN KEY([VoiceSystemID])
REFERENCES [dbo].[VoiceSystem] ([ID])
GO
ALTER TABLE [dbo].[ReportRunLog] CHECK CONSTRAINT [FK_Run_Switch]
GO
ALTER TABLE [dbo].[ReportRunLog]  WITH CHECK ADD  CONSTRAINT [FK_RunLog_ReportRunner] FOREIGN KEY([ReportRunnerID])
REFERENCES [dbo].[ReportRunner] ([ID])
GO
ALTER TABLE [dbo].[ReportRunLog] CHECK CONSTRAINT [FK_RunLog_ReportRunner]
GO
ALTER TABLE [dbo].[ReportRunner]  WITH CHECK ADD  CONSTRAINT [FK_ReportRunner_Switch] FOREIGN KEY([VoiceSystemID])
REFERENCES [dbo].[VoiceSystem] ([ID])
GO
ALTER TABLE [dbo].[ReportRunner] CHECK CONSTRAINT [FK_ReportRunner_Switch]
GO
ALTER TABLE [dbo].[Trap]  WITH CHECK ADD  CONSTRAINT [FK_Alarm_AlarmElement] FOREIGN KEY([ElementID])
REFERENCES [dbo].[Element] ([ID])
GO
ALTER TABLE [dbo].[Trap] CHECK CONSTRAINT [FK_Alarm_AlarmElement]
GO
ALTER TABLE [dbo].[TrapMonitor]  WITH CHECK ADD  CONSTRAINT [FK_AlarmNotification_AlarmElement] FOREIGN KEY([ElementID])
REFERENCES [dbo].[Element] ([ID])
GO
ALTER TABLE [dbo].[TrapMonitor] CHECK CONSTRAINT [FK_AlarmNotification_AlarmElement]
GO
ALTER TABLE [dbo].[TrapMonitor]  WITH CHECK ADD  CONSTRAINT [FK_AlarmNotification_EscalationProfile] FOREIGN KEY([EscalationProfileID])
REFERENCES [dbo].[EscalationProfile] ([ID])
GO
ALTER TABLE [dbo].[TrapMonitor] CHECK CONSTRAINT [FK_AlarmNotification_EscalationProfile]
GO
ALTER TABLE [dbo].[VoiceSystemSession]  WITH CHECK ADD  CONSTRAINT [FK_SwitchSession_Switch] FOREIGN KEY([VoiceSystemID])
REFERENCES [dbo].[VoiceSystem] ([ID])
GO
ALTER TABLE [dbo].[VoiceSystemSession] CHECK CONSTRAINT [FK_SwitchSession_Switch]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'This table is used to put commands that will run to a specific switch report.  It is not the best but will work for now.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ReportCommand'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Number of times this report was tried before success or failure.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ReportRunLog', @level2type=N'COLUMN',@level2name=N'RunAttempts'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ReportRunLog'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Only hours and minutes the format is 1-hour, 2-minute, 90-minute' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ReportRunner', @level2type=N'COLUMN',@level2name=N'RunTime'

